package Database;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class DatabaseFactory {

	private StringBuilder finalQuery;
	private ResultSet result;
	private Statement statement;	
	/*
	 * Initial queries
	 */
	private final String EVENT_SELECT_QUERY = "SELECT * FROM `Event` ";
	private final String EVENT_INSERT_QUERY = "INSERT INTO `Event` ";
	private final String EVENT_DELETE_QUERY = "DELETE FROM `Event` WHERE ";
	private final String EVENT_UPDATE_QUERY = "UPDATE `Event` SET ";

	private final String RESERVATION_SELECT_QUERY = "SELECT * FROM `Reservation` ";
	private final String RESERVATION_INSERT_QUERY = "INSERT INTO `Reservation` ";
	private final String RESERVATION_DELETE_QUERY = "DELETE FROM `Reservation` WHERE ";
	
	public DatabaseFactory(Connection con) throws SQLException{
		try{
			this.statement = con.createStatement();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	public String viewEvents() {
		StringBuilder feedback = new StringBuilder();
		Event event = null;
		finalQuery = new StringBuilder();
		finalQuery.append(EVENT_SELECT_QUERY);
		try{
			result = this.statement.executeQuery(finalQuery.toString());
			while(result.next()){
				event = new Event();
				event.setDescription(result.getString("description"));
				event.setEvent_number(result.getInt("event_number"));
				event.setTOTAL_SPACE(result.getInt("total_space"));
				event.setAVAILABLE_SPACE(result.getInt("available_space"));
				feedback.append(event.toString());
			}
			//close resources
			result.close();
			statement.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		if(feedback==null) return "**Currently, there are no events in our database**\n";	
		return feedback.toString();
	}
	public String addEvent(Event newEvent){
		//Build Mysql query string
		finalQuery = new StringBuilder();
		finalQuery.append(EVENT_INSERT_QUERY);
		finalQuery.append("(description, total_space, available_space) ");
        finalQuery.append("VALUES ");
        finalQuery.append("(");
        finalQuery.append("'").append(newEvent.getDescription()).append("',");
        finalQuery.append("'").append(newEvent.getTOTAL_SPACE()).append("',");
        //total_space == available_space for a new event
        finalQuery.append("'").append(newEvent.getTOTAL_SPACE()).append("')");
        int event_number = -1;
        try{
        	int rowsAffected = this.statement.executeUpdate(finalQuery.toString());
        	//verify changes made to the database
        	if(rowsAffected == 0) return "Could not add Event #" + newEvent.getEvent_number() + "";
        	
        	result = this.statement.executeQuery(EVENT_SELECT_QUERY + "WHERE description='" +
        			newEvent.getDescription() + "'");
	        while(result.next()){
	        	event_number = result.getInt("event_number");
	        }
        	
        	result.close();
        	statement.close();
        }catch(SQLException e){
        	e.printStackTrace();
        }
		return "Event #" + event_number + " added!";
	}
	public Event getEvent(int Event_number){
		Event event = null;
		finalQuery = new StringBuilder();
		finalQuery.append(EVENT_SELECT_QUERY);
		finalQuery.append("WHERE event_number= \"").append(Event_number).append("\"");		
        try{
            result = this.statement.executeQuery(finalQuery.toString());
            while(result.next()){
            	event = new Event();
            	event.setDescription(result.getString("description"));
				event.setEvent_number(result.getInt("event_number"));
				event.setTOTAL_SPACE(result.getInt("total_space"));
				event.setAVAILABLE_SPACE(result.getInt("available_space"));
            }
            //close resources
            result.close();
            statement.close();
        }catch(SQLException e){
        	e.printStackTrace();
        }
        return event;
	}
	public String deleteEvent(int Event_number){
		finalQuery = new StringBuilder();
		finalQuery.append(EVENT_DELETE_QUERY);
		finalQuery.append("event_number='" + Event_number + "'");
		try{
			int rowsAffected = this.statement.executeUpdate(finalQuery.toString());
			//verify database modification
			if(rowsAffected == 0) return "Could not Delete Event #" + Event_number +
				". Event does not exist";
	        //close resources
			statement.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return "Event #" + Event_number + " Deleted!";
	}
	
	public int addReservation(Event theEvent, String name,int space_amount){
		int reservation_number = -1;
		
		finalQuery = new StringBuilder();
		finalQuery.append(RESERVATION_INSERT_QUERY);
		finalQuery.append("(event,space_amount,name) ");
		finalQuery.append("VALUES ");
		finalQuery.append("(");
		finalQuery.append("'").append(theEvent.getEvent_number()).append("',");
		finalQuery.append("'").append(space_amount).append("',");
		finalQuery.append("'").append(name).append("')");
		
		try{
			//add the reservation to the database
			int rowsAffected = this.statement.executeUpdate(finalQuery.toString());
			//retrieve auto generated reservation number
			result = this.statement.executeQuery(RESERVATION_SELECT_QUERY + "WHERE event='" + theEvent.getEvent_number() + "' AND name='" + name + "'");
			while(result.next()){
				reservation_number = result.getInt("reservation_number");
			}
			
			update_space(theEvent.getEvent_number(),space_amount, false);
			//close resources
			result.close();
			statement.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return reservation_number;
	}

	public void update_space(int event_number, int space, boolean add){
		finalQuery = new StringBuilder();
		String operator = "+";
		
		if(!add) operator = "-";
		//UPDATE `Event` SET available_space = available_space-20 WHERE event_number='7'
		finalQuery.append(EVENT_UPDATE_QUERY + "available_space = available_space" + operator + space);
		finalQuery.append(" WHERE event_number='" + event_number + "'");
		try{
			int rowsAffected = this.statement.executeUpdate(finalQuery.toString());
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

	
	public String deleteReservation(int reservation_number){
		String feedback = "";
		int restore_seats = 0;
		int event = 0;
		
		//deletion query
		finalQuery = new StringBuilder();
		finalQuery.append(RESERVATION_DELETE_QUERY);
		//FIXED QUERY HERE, seems like there where 2 WHERE substrings in the query
		finalQuery.append("reservation_number = '").append(reservation_number).append("'");
		
		try{
			//retrieve information reagarding the reservation to be deleted. Event # and number of spaces in the reservation.
			result = this.statement.executeQuery(RESERVATION_SELECT_QUERY + "where reservation_number = '" + 
						reservation_number + "'");
			while(result.next()){
				event = result.getInt("event");
				restore_seats = result.getInt("space_amount");
			}
			
			//delete the reservation from the database
			int rowsAffected = this.statement.executeUpdate(finalQuery.toString());
			
			//restore seats
			update_space(event,restore_seats, true);
			
			//produce feedback
			feedback = feedback +"Notification: Reservation has been deleted.\n" +
			 "Event number :"+event+"\n"+
			 "Space made available: "+restore_seats+"\n";
			//close resources
			result.close();
			statement.close();			
		}catch(SQLException e){
			e.printStackTrace();
		}
		return feedback;
	}
	
	public boolean getReservation(int reservation_number){

		finalQuery = new StringBuilder();
		finalQuery.append(RESERVATION_SELECT_QUERY);
		finalQuery.append("WHERE reservation_number = '").append(reservation_number).append("'");
		try{
			result = this.statement.executeQuery(finalQuery.toString());
			if(result.next()) return true;
			
			//close resources
			result.close();
			statement.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
	}
	
	
	//used for listing reservations for a particular event,
	//under detail.
	public String getReserforEvent(int Event_number){
		finalQuery = new StringBuilder();
		finalQuery.append(RESERVATION_SELECT_QUERY);
		finalQuery.append("WHERE event='"+Event_number+"'");
		
		StringBuilder feedback = new StringBuilder();
		try{
			result = this.statement.executeQuery(finalQuery.toString());
			while(result.next()){
				feedback.append("\nRESERVATION INFO.\n-------------------\n");
				feedback.append("Reservation for " + result.getString("name"));
				feedback.append("\nReserved seats: " + result.getInt("space_amount"));
				feedback.append("\n");
			}
			//close resources
			this.result.close();
			this.statement.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		if(feedback.toString().isEmpty()) return "";
		return feedback.toString();
	}

}
